<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Programming with JDBC</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Apache Derby, Java, tutorial, database, programming with JDBC">
<meta name="description" content="In this part of the Apache Derby tutorial, we will create 
Java programs which will work with the Derby database.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Programming with JDBC</h1>

<p>
In this chapter, we will create Java programs which will work
with the Derby database. 
</p>

<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* NewSquare */
google_ad_slot = "0364418177";
google_ad_width = 300;
google_ad_height = 250;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 


<p>
The examples are executed in the Derby embedded mode. We use the
derby.jar library to connect to the Derby engine in the embedded mode.
</p>


<h2>JDBC</h2>

<p>
JDBC is an API for the Java programming language that defines how a client 
may access a database. It provides methods for querying and updating data in 
a database. JDBC is oriented towards relational databases. From a technical 
point of view, the API is as a set of classes in the java.sql package. 
To use JDBC with a particular database, we need a JDBC driver for that database.
</p>


<h2>Creating the CARS table</h2>

<p>
In the first example, we will create a CARS table and insert 8 rows into it.
Drop the CARS table from the database if it is already created before running
the example.
</p>

<pre class="code">
package zetcode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CreateCars {
    
    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;

        String url = "jdbc:derby:testdb;user=USER12";

        try {
            
            System.setProperty("derby.system.home", 
                    "/home/janbodnar/programming/derby/dbs");
            
            con = DriverManager.getConnection(url);
            st = con.createStatement();
            st.executeUpdate("CREATE TABLE CARS(ID INT PRIMARY KEY,"
                    + "NAME VARCHAR(30), PRICE INT)");
            st.executeUpdate("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
            st.executeUpdate("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
            st.executeUpdate("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
            st.executeUpdate("INSERT INTO CARS VALUES(4, 'Volvo', 29000)");
            st.executeUpdate("INSERT INTO CARS VALUES(5, 'Bentley', 350000)");
            st.executeUpdate("INSERT INTO CARS VALUES(6, 'Citroen', 21000)");
            st.executeUpdate("INSERT INTO CARS VALUES(7, 'Hummer', 41400)");
            st.executeUpdate("INSERT INTO CARS VALUES(8, 'Volkswagen', 21600)");
            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {
            
            Logger lgr = Logger.getLogger(CreateCars.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {

                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(CreateCars.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
The example connects to the Derby in embedded mode. Creates a CARS table
and adds 8 rows into it. Finally, it shuts down Derby. 
</p>

<pre class="explanation">
String url = "jdbc:derby:testdb;user=USER12";
</pre>

<p>
This is the URL to connect to the testdb database. In the embedded mode
and USER12 schema. 
</p>

<pre class="explanation">
System.setProperty("derby.system.home", 
        "/home/janbodnar/programming/derby/dbs");
</pre>

<p>
We set the system property for the Derby system directory. 
</p>

<pre class="explanation">
con = DriverManager.getConnection(url);
</pre>

<p>
A connection to the Derby database is created. When the connection is 
created, the Derby database is booted. 
</p>

<pre class="explanation">
st.executeUpdate("CREATE TABLE CARS(ID INT PRIMARY KEY,"
        + "NAME VARCHAR(30), PRICE INT)");
st.executeUpdate("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
...
</pre>

<p>
We execute the SQL statements which create the database and fill it with 
some data. For INSERT, UPDATE and DELETE statements and DDL statements
like CREATE TABLE we use the executeUpdate() method.
</p>

<pre class="explanation">
DriverManager.getConnection("jdbc:derby:;shutdown=true");
</pre>

<p>
The Derby database engine is shut down. 
</p>

<pre class="explanation">
} catch (SQLException ex) {
    
    Logger lgr = Logger.getLogger(CreateCars.class.getName());
</pre>

<p>
We catch the SQLException. We use the Logger class to log the
error message. 
</p>

<pre class="explanation">
if (((ex.getErrorCode() == 50000)
        &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

    lgr.log(Level.INFO, "Derby shut down normally", ex);

} 
</pre>

<p>
When the Derby engine is shut down, an SQLException is thrown. 
We catch this exception and log an information message. 
</p>

<pre class="explanation">
} finally {

    try {

        if (st != null) {
            st.close();
        }
        if (con != null) {
            con.close();
        }
</pre>

<p>
In the finally clause, we release the resources. 
</p>

<pre>
$ javac zetcode/CreateCars.java
$ java -cp .:lib/derby.jar zetcode.CreateCars 
Feb 17, 2012 11:34:02 PM zetcode.CreateCars main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
</pre>

<p>
We compile and run the example. The shut down of Derby will end
in an SQLException. This is a feature of the Derby database. 
</p>


<h2>Retrieving data</h2>

<p>
Next we will show, how to retrieve data from a database table. 
We get all data from the CARS table.
</p>


<pre class="code">
package zetcode;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SelectAllCars {
    
    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:derby:testdb";

        try {
            
            System.setProperty("derby.system.home", 
                    "/home/janbodnar/programming/derby/dbs");
            
            con = DriverManager.getConnection(url);
            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM USER12.CARS");

            while (rs.next()) {
                System.out.print(rs.getInt(1));
                System.out.print(" ");
                System.out.print(rs.getString(2));
                System.out.print(" ");
                System.out.println(rs.getString(3));
            }
            
            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {
            
            Logger lgr = Logger.getLogger(SelectAllCars.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(SelectAllCars.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
We get all the cars from the CARS table and print them to the console. 
</p>

<pre class="explanation">
st = con.createStatement();
rs = st.executeQuery("SELECT * FROM USER12.CARS");
</pre>

<p>
We execute a query that selects all columns from the CARS table. 
We use the executeQuery() method. The method executes the given SQL 
statement, which returns a single ResultSet object. The ResultSet is 
the data table returned by the SQL query. Also note that since we have
not specified the user name in the URL, we have to explicitly mention
the schema name in the SQL statement.
</p>

<pre class="explanation">
while (rs.next()) {
    System.out.print(rs.getInt(1));
    System.out.print(" ");
    System.out.print(rs.getString(2));
    System.out.print(" ");
    System.out.println(rs.getString(3));
}
</pre>

<p>
The next() method advances the cursor to the next record of the result set.
It returns false, when there are no more rows in the result set. 
The getInt() and getString() methods retrieve the value of the 
designated column in the current row of this ResultSet object; 
an int/String in the Java programming language.
</p>

<pre>
$ javac zetcode/SelectAllCars.java 
$ java -cp .:lib/derby.jar zetcode.SelectAllCars
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
Feb 18, 2012 12:33:31 AM zetcode.SelectAllCars main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
</pre>

<p>
We compile and run the example. We have a list of all cars
from the CARS table of the testdb database. 
</p>


<h2>Properties</h2>

<p>
It is a common practice to put the configuration data outside the program 
in a separate file. This way the programmers are more flexible. We can change 
the user, a password or a connection url without needing to recompile the program. 
It is especially useful in a dynamic environment, where is a need for a 
lot of testing, debugging, securing data etc.
</p>

<p>
In Java, the Properties is a class used often for this. 
The class is used for easy reading and saving of key/value properties.
</p>

<pre>
db.url=jdbc:derby:testdb;user=USER12
db.user=USER12
db.passwd=34klq*
db.syshome=/home/janbodnar/programming/derby/dbs
</pre>

<p>
We have a database.properties file, in which we have four key/value pairs. 
These are dynamically loaded during the execution of the program.
</p>

<pre class="code">
package zetcode;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class PropertiesExample {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;


        Properties props = new Properties();
        FileInputStream in = null;

        try {

            in = new FileInputStream("database.properties");
            props.load(in);

        } catch (FileNotFoundException ex) {

            Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } catch (IOException ex) {

            Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {

            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException ex) {
                Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        }

        String url = props.getProperty("db.url");
        String user = props.getProperty("db.user");
        String passwd = props.getProperty("db.passwd");

        try {

            System.setProperty("derby.system.home",
                    props.getProperty("db.syshome"));

            con = DriverManager.getConnection(url, user, passwd);
            pst = con.prepareStatement("SELECT * FROM CARS");
            rs = pst.executeQuery();

            while (rs.next()) {
                System.out.print(rs.getInt(1));
                System.out.print(": ");
                System.out.println(rs.getString(2));
            }

            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
            
            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {
                
                lgr.log(Level.INFO, "Derby shut down normally", ex);
                
            } else {
                                
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }                        

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {

                Logger lgr = Logger.getLogger(PropertiesExample.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
We connect to the testdb and select all authors from the AUTHORS table. 
The configuration data for the example is read from the database.properties file.
</p>

<pre class="explanation">
Properties props = new Properties();
FileInputStream in = null;

try {

    in = new FileInputStream("database.properties");
    props.load(in);
</pre>

<p>
The Properties class is created. The data is loaded from the file called 
database.properties, where we have our configuration data.
</p>

<pre class="explanation">
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
</pre>

<p>
The values are retrieved with the getProperty() method.
</p>

<pre class="explanation">
con = DriverManager.getConnection(url, user, passwd);
</pre>

<p>
Note that in the default Derby configuration, the password is
ignored. 
</p>


<h2>Prepared statements</h2>

<p>
Now we will concern ourselves with prepared statements. When we write 
prepared statements, we use placeholders instead of directly writing 
the values into the statements. Prepared statements increase 
security and performance.
</p>

<p>
In Java a PreparedStatement is an object which represents a precompiled SQL statement.
</p>

<pre class="code">
package zetcode;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;


public class Prepared {
    
    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String url = "jdbc:derby:testdb;user=USER12";

        int price = 58000;
        int id = 2;
        
        try {
            
            System.setProperty("derby.system.home", 
                    "/home/janbodnar/programming/derby/dbs");
            
            con = DriverManager.getConnection(url);
     
            pst = con.prepareStatement("UPDATE CARS SET PRICE = ? WHERE ID = ?");
            pst.setInt(1, price);
            pst.setInt(2, id);
            pst.executeUpdate();           
            
            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {
            
            Logger lgr = Logger.getLogger(Prepared.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Prepared.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
We change the price for a car with id equal to 2. 
</p>

<pre class="explanation">
int price = 58000;
int id = 2;
</pre>

<p>
These are the values that are going to be set to the prepared
statement. These values could come from a user and everything 
coming from users should be considered potentionally dangerous.
</p>

<pre class="explanation">
pst = con.prepareStatement("UPDATE CARS SET PRICE = ? WHERE ID = ?");
</pre>

<p>
Here we create a prepared statement. When we write prepared statements, 
we use placeholders instead of directly writing the values into the 
statements. Prepared statements are faster and guard against SQL injection 
attacks. The ? is a placeholder, which is going to be filled later.
</p>

<pre class="explanation">
pst.setInt(1, price);
pst.setInt(2, id);
</pre>

<p>
Values are bound to the placeholders.
</p>

<pre class="explanation">
pst.executeUpdate();
</pre>

<p>
The prepared statement is executed. We use the executeUpdate() method of 
the statement object when we don't expect any data to be returned. 
This is when we create databases or execute INSERT, UPDATE, DELETE statements.
</p>

<pre>
$ javac zetcode/Prepared.java
$ java -cp .:lib/derby.jar zetcode.Prepared 
Feb 18, 2012 11:08:47 AM zetcode.Prepared main
SEVERE: Database 'testdb' shutdown.
java.sql.SQLNonTransientConnectionException: Database 'testdb' shutdown.
...

ij> SELECT * FROM CARS WHERE ID=2;
ID         |NAME                          |PRICE      
------------------------------------------------------
2          |Mercedes                      |58000      

1 row selected
</pre>

<p>
We compile the example. Run it. And check the outcome with the ij tool.
</p>


<h2>Column headers</h2>

<p>
Next we will show, how to print column headers with the data from the database table. 
We refer to column names as MetaData. 
MetaData is data about the core data in the database.
</p>


<pre class="code">
package zetcode;

import java.sql.*;
import java.util.Formatter;
import java.util.logging.Level;
import java.util.logging.Logger;


public class ColumnHeaders {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String url = "jdbc:derby:testdb;user=USER12";

        try {

            System.setProperty("derby.system.home", 
                    "/home/janbodnar/programming/derby/dbs");

            con = DriverManager.getConnection(url);
            String query = "SELECT NAME, TITLE From AUTHORS, "
                    + "Books WHERE AUTHORS.ID=BOOKS.AUTHOR_ID";
            pst = con.prepareStatement(query);

            rs = pst.executeQuery();

            ResultSetMetaData meta = rs.getMetaData();

            String colname1 = meta.getColumnName(1);
            String colname2 = meta.getColumnName(2);

            Formatter fmt1 = new Formatter();
            fmt1.format("%-21s%s", colname1, colname2);
            System.out.println(fmt1);

            while (rs.next()) {
                Formatter fmt2 = new Formatter();
                fmt2.format("%-21s", rs.getString(1));
                System.out.print(fmt2);
                System.out.println(rs.getString(2));
            }

            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(ColumnHeaders.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(ColumnHeaders.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
In this program, we select authors from the AUTHORS table and their 
books from the BOOKS table. We print the names of the columns 
returned in the result set. We format the output. The SQL file to create the
tables is located in the first chapter of this tutorial. 
</p>

<pre class="explanation">
String query = "SELECT NAME, TITLE From AUTHORS, "
        + "Books WHERE AUTHORS.ID=BOOKS.AUTHOR_ID";
</pre>

<p>
This is the SQL statement which joins authors with their books.
</p>

<pre class="explanation">
ResultSetMetaData meta = rs.getMetaData();
</pre>

<p>
To get the column names we need to get the ResultSetMetaData. 
It is an object that can be used to get information about the types 
and properties of the columns in a ResultSet object.
</p>

<pre class="explanation">
String colname1 = meta.getColumnName(1);
String colname2 = meta.getColumnName(2);
</pre>

<p>
From the obtained metadata, we get the column names.
</p>

<pre class="explanation">
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1);
</pre>

<p>
We print the column names to the console. The Formatter object formats the data.
</p>

<pre class="explanation">
while (rs.next()) {
    Formatter fmt2 = new Formatter();
    fmt2.format("%-21s", rs.getString(1));
    System.out.print(fmt2);
    System.out.println(rs.getString(2));
}
</pre>

<p>
We print the data to the console. We again use the Formatter object to 
format the data. The first column is 21 characters wide and is aligned to the left.
</p>

<pre>
$ javac zetcode/ColumnHeaders.java
$ java -cp .:lib/derby.jar zetcode.ColumnHeaders 
NAME                 TITLE
Jack London          Call of the Wild
Jack London          Martin Eden
Honore de Balzac     Old Goriot
Honore de Balzac     Cousin Bette
Lion Feuchtwanger    Jew Suess
Emile Zola           Nana
Emile Zola           The Belly of Paris
Truman Capote        In Cold blood
Truman Capote        Breakfast at Tiffany
Feb 18, 2012 12:15:21 PM zetcode.ColumnHeaders main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...
</pre>

<p>
The example is compiled and executed.
</p>


<h2>Writing images</h2>

<p>
Some people prefer to put their images into the database, some prefer 
to keep them on the file system for their applications. Technical difficulties 
arise when we work with millions of images. Images are binary data. 
Derby has a special data type to store binary data called BLOB 
(Binary Large Object).
</p>

<p>
We create a new table called IMAGES for this and the following example.
</p>

<pre>
ij> CREATE TABLE IMAGES(ID INT PRIMARY KEY, DATA BLOB);
0 rows inserted/updated/deleted
</pre>

<p>
The DATA column has the BLOB type. There we will insert the encoded binary
data. 
</p>

<pre class="code">
package zetcode;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class WriteImage {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;

        FileInputStream fin = null;

        String url = "jdbc:derby:testdb;user=USER12";


        try {

            System.setProperty("derby.system.home",
                    "/home/janbodnar/programming/derby/dbs");

            con = DriverManager.getConnection(url);

            File img = new File("woman.jpg");
            fin = new FileInputStream(img);

            con = DriverManager.getConnection(url);

            pst = con.prepareStatement("INSERT INTO IMAGES(ID, DATA) VALUES(1, ?)");
            pst.setBinaryStream(1, fin, (int) img.length());
            pst.executeUpdate();

            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (FileNotFoundException ex) {
            
            Logger lgr = Logger.getLogger(WriteImage.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(WriteImage.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {

                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(WriteImage.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
In this example, we read a jpg image from the current working directory 
and insert in into the IMAGES table.
</p>

<pre class="explanation">
File img = new File("woman.jpg");
fin = new FileInputStream(img);
</pre>

<p>
We create a File object for the image file. To read bytes from 
this file, we create a FileInputStream object.
</p>

<pre class="explanation">
pst = con.prepareStatement("INSERT INTO IMAGES(ID, DATA) VALUES(1, ?)");
</pre>

<p>
This SQL statement inserts the image into the 
</p>

<pre class="explanation">
pst.setBinaryStream(1, fin, (int) img.length());
</pre>

<p>
The binary stream is set to the prepared statement. The parameters of the 
setBinaryStream() method are the parameter index to bind, the input stream 
and the number of bytes in the stream.
</p>


<pre class="explanation">
pst.executeUpdate();
</pre>

<p>
We execute the statement.
</p>

<h2>Reading images</h2>

<p>
In the previous example, we have inserted an image into the database table. Now we are 
going to read the image back from the table.
</p>

<pre class="code">
package zetcode;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ReadImage {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        FileOutputStream fos = null;

        String url = "jdbc:derby:testdb;user=USER12";


        try {

            System.setProperty("derby.system.home",
                    "/home/janbodnar/programming/derby/dbs");

            System.out.println(System.getProperty("user.dir"));

            con = DriverManager.getConnection(url);

            String query = "SELECT DATA FROM IMAGES WHERE ID = 1";
            pst = con.prepareStatement(query);

            ResultSet result = pst.executeQuery();
            result.next();

            fos = new FileOutputStream("woman2.jpg");

            Blob blob = result.getBlob("DATA");
            int len = (int) blob.length();

            byte[] buf = blob.getBytes(1, len);

            fos.write(buf, 0, len);

            DriverManager.getConnection("jdbc:derby:;shutdown=true");


        } catch (IOException ex) {
            Logger lgr = Logger.getLogger(ReadImage.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(ReadImage.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(ReadImage.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
We read one image from the IMAGES table.
</p>

<pre class="explanation">
String query = "SELECT DATA FROM IMAGES WHERE ID = 1";
</pre>

<p>
One record is selected. 
</p>

<pre class="explanation">
fos = new FileOutputStream("woman2.jpg");
</pre>

<p>
The FileOutputStream object is created to write to a file. 
It is meant for writing streams of raw bytes such as image data.
</p>

<pre class="explanation">
Blob blob = result.getBlob("DATA");
</pre>

<p>
We get the image data from the DATA column by calling the getBlob() method.
</p>

<pre class="explanation">
int len = (int) blob.length();
</pre>

<p>
We find out the length of the blob data. In other words, we get the number of bytes.
</p>

<pre class="explanation">
byte[] buf = blob.getBytes(1, len);
</pre>

<p>
The getBytes() method retrieves all bytes of the BLOB object, as an array of bytes.
</p>

<pre class="explanation">
fos.write(buf, 0, len);
</pre>

<p>
The bytes are written to the output stream. The image is created on the filesystem.
</p>


<h2>Transaction support</h2>

<p>
A transaction is an atomic unit of database operations against the data in one or 
more databases. The effects of all the SQL statements in a transaction can be 
either all committed to the database or all rolled back.
</p>

<p>
When a connection is created, it is in autocommit mode. This means that each 
individual SQL statement is treated as a transaction and is automatically 
committed right after it is executed. This is true for all JDBC drivers, 
including the Derby's one. To start a new transaction, we turn the 
autocommit off.
</p>

<p>
In direct SQL, a transaction is started with BEGIN TRANSACTION statement and 
ended with END TRANSACTION/COMMIT statement. In Derby these statements 
are BEGIN and COMMIT. However, when working with drivers these statements are 
omitted. They are handled by the driver. Exact details are specific to the driver.
For example psycopg2 Python driver starts a transaction after the first SQL statement. 
If we want the autocommit mode, we must be set the autocommit property to True. 
In constrast, JDBC driver is by default in the autocommit mode. And to start 
a new transaction, the autocommit must be turned off.
</p>

<pre class="code">
package zetcode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Transaction {

    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;

        String url = "jdbc:derby:testdb;user=USER12";


        try {

            System.setProperty("derby.system.home",
                    "/home/janbodnar/programming/derby/dbs");

            con = DriverManager.getConnection(url);

            st = con.createStatement();

            con.setAutoCommit(false);

            st.executeUpdate("UPDATE AUTHORS SET NAME = 'Leo Tolstoy' "
                    + "WHERE Id = 1");
            st.executeUpdate("UPDATE BOOKS SET TITLE = 'War and Peace' "
                    + "WHERE Id = 1");
            st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
                    + "WHERE Id = 2");

            con.commit();

            DriverManager.getConnection("jdbc:derby:;shutdown=true");


        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(Transaction.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {
                
                if (con != null) {
                    try {
                        con.rollback();
                    } catch (SQLException ex1) {
                        lgr.log(Level.WARNING, ex1.getMessage(), ex1);
                    }
                }
                
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Transaction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
In this program, we want to change the name of the author in the first row of 
the AUTHORS table. We must also change the books associated with this author. 
If we change the author and do not change the author's books, the data is corrupted.
</p>

<pre class="explanation">
con.setAutoCommit(false);
</pre>

<p>
To work with transactions, we must set the autocommit to false. By default, a database 
connection is in autocommit mode. In this mode each statement is committed to the 
database, as soon as it is executed. A statement cannot be undone. When the 
autocommit is turned off, we commit the changes by calling the commit() or 
roll it back by calling the rollback() method.
</p>

<pre class="explanation">
st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
                    + "WHERE Id = 2");
</pre>

<p>
The third SQL statement has an error. There is no TITL column in the BOOKS table.
</p>

<pre class="explanation">
con.commit();
</pre>

<p>
If there is no exception, the transaction is committed. If the autocommit is 
turned off, we must explicitly call the commit() method.
</p>

<pre class="explanation">
if (con != null) {

    try {
        con.rollback();
    } catch (SQLException ex1) {
        lgr.log(Level.WARNING, ex1.getMessage(), ex1);
    }
}
</pre>

<p>
In case of an exception other than the Derby system shutdown,
the transaction is rolled back. No changes are committed to the database.
</p>


<pre>
$ javac zetcode/Transaction.java
$ java -cp .:lib/derby.jar zetcode.Transaction 
Feb 18, 2012 3:02:05 PM zetcode.Transaction main
SEVERE: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
java.sql.SQLSyntaxErrorException: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
...

ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS WHERE
> AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME                     |TITLE                                                                                               
------------------------------------------------------------
Jack London              |Call of the Wild                                                                                    
Jack London              |Martin Eden                                                                                         
Honore de Balzac         |Old Goriot                                                                                          
Honore de Balzac         |Cousin Bette                                                                                        
Lion Feuchtwanger        |Jew Suess                                                                                           
Emile Zola               |Nana                                                                                                
Emile Zola               |The Belly of Paris                                                                                  
Truman Capote            |In Cold blood                                                                                       
Truman Capote            |Breakfast at Tiffany                                                                                

9 rows selected

</pre>

<p>
The execution fails with the "'TITL' is not a column in table" message.
An exception was thrown. The transaction was rolled back and no 
changes took place.
</p>

<hr class="btm">

<p>
However, without a transaction, the data is not safe.
</p>

<pre class="code">
package zetcode;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class NonTransaction {

    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;

        String url = "jdbc:derby:testdb;user=USER12";


        try {

            System.setProperty("derby.system.home",
                    "/home/janbodnar/programming/derby/dbs");

            con = DriverManager.getConnection(url);

            st = con.createStatement();

            st.executeUpdate("UPDATE AUTHORS SET NAME = 'Leo Tolstoy' "
                    + "WHERE Id = 1");
            st.executeUpdate("UPDATE BOOKS SET TITLE = 'War and Peace' "
                    + "WHERE Id = 1");
            st.executeUpdate("UPDATE BOOKS SET TITL = 'Anna Karenina' "
                    + "WHERE Id = 2");

            DriverManager.getConnection("jdbc:derby:;shutdown=true");


        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(NonTransaction.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {

                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(NonTransaction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
We have the same example. This time, without the transaction support.
</p>


<pre>
$ javac zetcode/NonTransaction.java
$ java -cp .:lib/derby.jar zetcode.NonTransaction
Feb 18, 2012 3:13:45 PM zetcode.NonTransaction main
SEVERE: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
java.sql.SQLSyntaxErrorException: 'TITL' is not a column in table or VTI 'USER12.BOOKS'.
...

ij> SELECT NAME, TITLE FROM AUTHORS, BOOKS WHERE
> AUTHORS.ID = BOOKS.AUTHOR_ID;
NAME                     |TITLE                                                                                               
----------------------------------------------------------------
Leo Tolstoy              |War and Peace                                                                                       
Leo Tolstoy              |Martin Eden                                                                                         
Honore de Balzac         |Old Goriot                                                                                          
Honore de Balzac         |Cousin Bette                                                                                        
Lion Feuchtwanger        |Jew Suess                                                                                           
Emile Zola               |Nana                                                                                                
Emile Zola               |The Belly of Paris                                                                                  
Truman Capote            |In Cold blood                                                                                       
Truman Capote            |Breakfast at Tiffany                                                                                

9 rows selected
</pre>

<p>
An exception is thrown again. Leo Tolstoy did not write Martin Eden. The data is corrupted.
</p>


<h2>Batch updates</h2>

<p>
When we need to update data with multiple statements, we can use batch updates. 
Batch updates are available for INSERT, UPDATE, DELETE statements as well as for 
CREATE TABLE and DROP TABLE statements.
</p>

<pre class="code">
package zetcode;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class BatchUpdates {

    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:derby:testdb;user=USER12";

        try {

            System.setProperty("derby.system.home",
                    "/home/janbodnar/programming/derby/dbs");

            con = DriverManager.getConnection(url);

            con.setAutoCommit(false);
            st = con.createStatement();

            st.addBatch("DELETE FROM CARS");
            st.addBatch("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
            st.addBatch("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
            st.addBatch("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
            st.addBatch("INSERT INTO CARS VALUES(4, 'Volvo', 29000)");
            st.addBatch("INSERT INTO CARS VALUES(5, 'Bentley', 350000)");
            st.addBatch("INSERT INTO CARS VALUES(6, 'Citroen', 21000)");
            st.addBatch("INSERT INTO CARS VALUES(7, 'Hummer', 41400)");
            st.addBatch("INSERT INTO CARS VALUES(8, 'Volkswagen', 21600)");
            st.addBatch("INSERT INTO CARS VALUES(9, 'Jaguar', 95000)");

            int counts[] = st.executeBatch();

            con.commit();

            System.out.println("Committed " + counts.length + " updates");

            DriverManager.getConnection("jdbc:derby:;shutdown=true");

        } catch (SQLException ex) {

            Logger lgr = Logger.getLogger(BatchUpdates.class.getName());

            if (((ex.getErrorCode() == 50000)
                    &amp;&amp; ("XJ015".equals(ex.getSQLState())))) {

                lgr.log(Level.INFO, "Derby shut down normally", ex);

            } else {
                
                if (con != null) {
                    try {
                        con.rollback();
                    } catch (SQLException ex1) {
                        lgr.log(Level.WARNING, ex1.getMessage(), ex1);
                    }
                }
                
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }

        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(BatchUpdates.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}
</pre>

<p>
This is an example program for a batch update. We delete all rows from the
CARS table and insert nine rows into it.
</p>

<pre class="explanation">
con.setAutoCommit(false);
</pre>

<p>
Autocommit should always be turned off when doing batch updates.
</p>

<pre class="explanation">
st.addBatch("DELETE FROM CARS");
st.addBatch("INSERT INTO CARS VALUES(1, 'Audi', 52642)");
st.addBatch("INSERT INTO CARS VALUES(2, 'Mercedes', 57127)");
st.addBatch("INSERT INTO CARS VALUES(3, 'Skoda', 9000)");
...
</pre>

<p>
We use teh addBatch() method to add a new command to the statement.
</p>

<pre class="explanation">
int counts[] = st.executeBatch();
</pre>

<p>
After adding all commands, we call the executeBatch() to perform 
a batch update. The method returns an array of committed changes.
</p>

<pre class="explanation">
con.commit();
</pre>

<p>
Batch updates are committed in a transaction.
</p>

<pre>
$ javac zetcode/BatchUpdates.java
$ java -cp .:lib/derby.jar zetcode.BatchUpdates Committed 10 updates
Feb 18, 2012 11:14:53 PM zetcode.BatchUpdates main
INFO: Derby shut down normally
java.sql.SQLException: Derby system shutdown.
...

ij> SELECT * FROM CARS;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |57127      
3          |Skoda                         |9000       
4          |Volvo                         |29000      
5          |Bentley                       |350000     
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      
9          |Jaguar                        |95000 
</pre>

<p>
We have successfully recreated the CARS table.
</p>


<p>
In the chapter, we did some JDBC programming with Java and Derby.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified March 4, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

